﻿using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;
using System.Linq;
using Dentiste.Domain.Entities;
using Dentiste.Domain.Extensions;
using Dentiste.Domain.Interfaces;

namespace Dentiste.Domain.Providers
{
    internal class InvoiceProvider : IProvider<IInvoice>
    {
        private readonly string _connectionString;
        private readonly PatientProvider _patientProvider;
        private readonly RowProvider _rowProvider;

        #region Constructor

        public InvoiceProvider(string connectionString, PatientProvider patientProvider, RowProvider rowProvider)
        {
            _connectionString = connectionString;
            _patientProvider = patientProvider;
            _rowProvider = rowProvider;
        }

        #endregion

        #region Select

        public DbSetCustom<IInvoice> Select()
        {
            const string queryString = "SELECT * FROM Invoices";
            var ret = new DbSetCustom<IInvoice>(this);
            using (var connection = new SqlCeConnection(_connectionString))
            {
                var cmd = new SqlCeCommand(queryString, connection);
                connection.Open();
                SqlCeDataReader reader = cmd.ExecuteReader();
                while (reader != null && reader.Read())
                {
                    int invoiceId = reader.GetInt32(0);
                    ret.Add(new Invoice
                    {
                        Id = invoiceId,
                        Date = DateTime.Parse(reader.GetString(1)),
                        Patient = _patientProvider.Select(Convert.ToString(reader.GetInt32(2)))
                        //Rows = GetAssociatedRows(invoiceId, connection)
                    });
                }
            }
            foreach (IInvoice invoice in ret)
            {
                invoice.Rows = GetAssociatedRows(invoice.Id);
            }
            return ret;
        }

        public IInvoice Select(string id)
        {
            const string queryString = "SELECT * FROM Invoices where Id=?";
            IInvoice i = null;
            using (var connection = new SqlCeConnection(_connectionString))
            {
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@Id", id);
                connection.Open();
                SqlCeDataReader reader = cmd.ExecuteReader();
                if (reader != null && reader.Read())
                {
                    int invoiceId = reader.GetInt32(0);
                    i = new Invoice
                    {
                        Id = invoiceId,
                        Date = DateTime.Parse(reader.GetString(1)),
                        Patient = _patientProvider.Select(Convert.ToString(reader.GetInt32(2)))
                        //Rows = GetAssociatedRows(invoiceId, connection)
                    };
                }
            }
            if (i != null)
            {
                i.Rows = GetAssociatedRows(i.Id);
            }
            return i;
        }

        private IList<IRow> GetAssociatedRows(int invoiceId)
        {
            const string queryString = "SELECT * FROM InvoiceRowRelations where InvoiceId=?";
            var ret = new List<IRow>();
            using (var connection = new SqlCeConnection(_connectionString))
            {
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@InvoiceId", invoiceId);

                connection.Open();

                SqlCeDataReader reader = cmd.ExecuteReader();
                IEnumerable<IRow> rows = _rowProvider.Select();

                if (reader != null)
                {
                    while (reader.Read())
                    {
                        int invoiceIdRead = reader.GetInt32(1);
                        int rowIdRead = reader.GetInt32(2);
                        if (invoiceIdRead == invoiceId)
                        {
                            ret.Add(rows.FirstOrDefault(r => r.Id == rowIdRead));
                        }
                    }
                }
            }
            return ret;
        }

        public IInvoice SelectLastInserted()
        {
            const string queryString = "SELECT max(id) FROM Invoices";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                var cmd = new SqlCeCommand(queryString, connection);
                connection.Open();
                SqlCeDataReader reader = cmd.ExecuteReader();
                if (reader != null && reader.Read())
                {
                    string id = Convert.ToString(reader.GetInt32(0));
                    return Select(id);
                }
            }
            return null;
        }

        #endregion

        #region Insert

        public IInvoice Insert(IInvoice entity)
        {
            int ret;
            const string queryString = "INSERT INTO Invoices (Date, PatientId) VALUES (?, ?)";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                connection.Open();
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@Date", entity.Date);
                cmd.Parameters.AddWithValue("@PatientId", entity.Patient != null ? entity.Patient.Id : -1);

                ret = cmd.ExecuteNonQuery();
            }
            PersistRowInvoiceAssociation(entity);
            if (ret == 1)
            {
                // Update id
                DbSetCustom<IInvoice> all = Select();
                foreach (IInvoice invoice in all)
                {
                    if (entity.Patient != null && (invoice.Date.CompareValuesTo(entity.Date) &&
                                                   (invoice.Patient == null || invoice.Patient.Id == entity.Patient.Id)))
                    {
                        entity.Id = invoice.Id;
                        return entity;
                    }
                }
                return SelectLastInserted();
            }
            return null;
        }

        public void PersistRowInvoiceAssociation(IInvoice entity)
        {
            const string queryString = "INSERT INTO InvoiceRowRelations (InvoiceId, RowId) VALUES (?, ?)";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                connection.Open();
                foreach (IRow row in entity.Rows)
                {
                    var cmd = new SqlCeCommand(queryString, connection);
                    cmd.Parameters.AddWithValue("@InvoiceId", entity.Id);
                    cmd.Parameters.AddWithValue("@RowId", row.Id);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        #endregion

        #region Update

        public bool Update(IInvoice entity)
        {
            int ret;
            const string queryString = "UPDATE Invoices SET Date=?,PatientId=? WHERE Id=?";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                connection.Open();
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@Date", entity.Date);
                cmd.Parameters.AddWithValue("@PatientId", entity.Patient.Id);
                cmd.Parameters.AddWithValue("@Id", entity.Id);

                ret = cmd.ExecuteNonQuery();
            }
            UpdateRowInvoiceAssociation(entity);
            return ret == 1;
        }

        private void UpdateRowInvoiceAssociation(IInvoice entity)
        {
            // Clean association for the current invoice
            DeleteRowInvoiceAssociation(entity);
            // Repopulate the association table
            PersistRowInvoiceAssociation(entity);
        }

        #endregion

        #region Delete

        public bool Delete(IInvoice entity)
        {
            int ret;
            const string queryString = "DELETE FROM Invoices WHERE Id=?";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                connection.Open();
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@Id", entity.Id);

                ret = cmd.ExecuteNonQuery();
            }
            DeleteRowInvoiceAssociation(entity);
            foreach (IRow row in entity.Rows)
            {
                _rowProvider.Delete(row);
            }
            return ret == 1;
        }

        private void DeleteRowInvoiceAssociation(IInvoice entity)
        {
            const string queryStringDelete = "DELETE FROM InvoiceRowRelations WHERE InvoiceId=?";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                connection.Open();
                var cmd = new SqlCeCommand(queryStringDelete, connection);
                cmd.Parameters.AddWithValue("@InvoiceId", entity.Id);
                cmd.ExecuteNonQuery();
            }
        }

        #endregion
    }
}